package org.csu.jpetstore.persistence.impl;

import org.csu.jpetstore.domain.Account;
import org.csu.jpetstore.persistence.AccountDAO;
import org.csu.jpetstore.persistence.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class AccountDAOImpl implements AccountDAO {

    private static final String FIND_USER_BY_USERNAME_AND_PASSWORD = "SELECT\n" +
            "      SIGNON.USERNAME,\n" +
            "      ACCOUNT.EMAIL,\n" +
            "      ACCOUNT.FIRSTNAME,\n" +
            "      ACCOUNT.LASTNAME,\n" +
            "      ACCOUNT.STATUS,\n" +
            "      ACCOUNT.ADDR1 AS address1,\n" +
            "      ACCOUNT.ADDR2 AS address2,\n" +
            "      ACCOUNT.CITY,\n" +
            "      ACCOUNT.STATE,\n" +
            "      ACCOUNT.ZIP,\n" +
            "      ACCOUNT.COUNTRY,\n" +
            "      ACCOUNT.PHONE,\n" +
            "      PROFILE.LANGPREF AS languagePreference,\n" +
            "      PROFILE.FAVCATEGORY AS favouriteCategoryId,\n" +
            "      PROFILE.MYLISTOPT AS listOption,\n" +
            "      PROFILE.BANNEROPT AS bannerOption,\n" +
            "      BANNERDATA.BANNERNAME\n" +
            "    FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA\n" +
            "    WHERE ACCOUNT.USERID = ?\n" +
            "      AND SIGNON.PASSWORD = ?\n" +
            "      AND SIGNON.USERNAME = ACCOUNT.USERID\n" +
            "      AND PROFILE.USERID = ACCOUNT.USERID\n" +
            "      AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";

    private static final String FIND_USER_BY_USERNAME = "SELECT\n" +
            "          SIGNON.USERNAME,\n" +
            "          ACCOUNT.EMAIL,\n" +
            "          ACCOUNT.FIRSTNAME,\n" +
            "          ACCOUNT.LASTNAME,\n" +
            "          ACCOUNT.STATUS,\n" +
            "          ACCOUNT.ADDR1 AS address1,\n" +
            "          ACCOUNT.ADDR2 AS address2,\n" +
            "          ACCOUNT.CITY,\n" +
            "          ACCOUNT.STATE,\n" +
            "          ACCOUNT.ZIP,\n" +
            "          ACCOUNT.COUNTRY,\n" +
            "          ACCOUNT.PHONE,\n" +
            "          PROFILE.LANGPREF AS languagePreference,\n" +
            "          PROFILE.FAVCATEGORY AS favouriteCategoryId,\n" +
            "          PROFILE.MYLISTOPT AS listOption,\n" +
            "          PROFILE.BANNEROPT AS bannerOption,\n" +
            "          BANNERDATA.BANNERNAME\n" +
            "    FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA\n" +
            "    WHERE ACCOUNT.USERID = ?\n" +
            "      AND SIGNON.USERNAME = ACCOUNT.USERID\n" +
            "      AND PROFILE.USERID = ACCOUNT.USERID\n" +
            "      AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";

    private static final String INSERT_ACCOUNT ="INSERT INTO ACCOUNT\n" +
            "      (EMAIL, FIRSTNAME, LASTNAME, STATUS, ADDR1, ADDR2, CITY, STATE, ZIP, COUNTRY, PHONE, USERID)\n" +
            "    VALUES\n" +
            "      (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    private static final String INSERT_PROFILE = "INSERT INTO PROFILE (LANGPREF, FAVCATEGORY, USERID)\n" +
            "    VALUES (?, ?, ?)";

    private static final String INSERT_SIGNON = "INSERT INTO SIGNON (PASSWORD,USERNAME)\n" +
            "    VALUES (?, ?)";

    private static final String UPDATE_ACCOUNT ="UPDATE ACCOUNT SET\n" +
            "      EMAIL = ?,\n" +
            "      FIRSTNAME = ?,\n" +
            "      LASTNAME = ?,\n" +
            "      STATUS = ?,\n" +
            "      ADDR1 = ?,\n" +
            "      ADDR2 = ?,\n" +
            "      CITY = ?,\n" +
            "      STATE = ?,\n" +
            "      ZIP = ?,\n" +
            "      COUNTRY = ?,\n" +
            "      PHONE = ?\n" +
            "    WHERE USERID = ?";

    private static final String UPDATE_PROFILE ="UPDATE PROFILE SET\n" +
            "      LANGPREF = ?,\n" +
            "      FAVCATEGORY = ?\n" +
            "    WHERE USERID = ?";

    private static final String UPDATE_SIGNON ="UPDATE SIGNON SET PASSWORD = ?\n" +
            "    WHERE USERNAME = ?";

    @Override
    public boolean getUserByUsername(String username) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(FIND_USER_BY_USERNAME);
            preparedStatement.setString(1,username);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()) {
                return true;
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch(Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public int addUser(Account account) {
        int result = 0;
        try {
            Connection connection = DBUtil.getConnection();

            PreparedStatement preparedStatement1 = connection.prepareStatement(INSERT_ACCOUNT);
            preparedStatement1.setString(1,account.getEmail());
            preparedStatement1.setString(2,account.getFirstName());
            preparedStatement1.setString(3,account.getLastName());
            preparedStatement1.setString(4,account.getStatus());
            preparedStatement1.setString(5,account.getAddress1());
            preparedStatement1.setString(6,account.getAddress2());
            preparedStatement1.setString(7,account.getCity());
            preparedStatement1.setString(8,account.getState());
            preparedStatement1.setString(9,account.getZip());
            preparedStatement1.setString(10,account.getCountry());
            preparedStatement1.setString(11,account.getPhone());
            preparedStatement1.setString(12,account.getUsername());

            PreparedStatement preparedStatement2 = connection.prepareStatement(INSERT_PROFILE);
            preparedStatement2.setString(1,account.getLanguagePreference());
            preparedStatement2.setString(2,account.getFavouriteCategoryId());
            preparedStatement2.setString(3,account.getUsername());

            PreparedStatement preparedStatement3 = connection.prepareStatement(INSERT_SIGNON);
            preparedStatement3.setString(1,account.getPassword());
            preparedStatement3.setString(2,account.getUsername());


            if(preparedStatement1.executeUpdate()==1 &&
               preparedStatement2.executeUpdate()==1 &&
               preparedStatement3.executeUpdate()==1)
                result=1;
            DBUtil.closePreparedStatement(preparedStatement1);
            DBUtil.closePreparedStatement(preparedStatement2);
            DBUtil.closePreparedStatement(preparedStatement3);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public int updateUser(Account account) {
        int result = 0;
        try {
            Connection connection = DBUtil.getConnection();

            PreparedStatement preparedStatement1 = connection.prepareStatement(UPDATE_ACCOUNT);
            preparedStatement1.setString(1,account.getEmail());
            preparedStatement1.setString(2,account.getFirstName());
            preparedStatement1.setString(3,account.getLastName());
            preparedStatement1.setString(4,account.getStatus());
            preparedStatement1.setString(5,account.getAddress1());
            preparedStatement1.setString(6,account.getAddress2());
            preparedStatement1.setString(7,account.getCity());
            preparedStatement1.setString(8,account.getState());
            preparedStatement1.setString(9,account.getZip());
            preparedStatement1.setString(10,account.getCountry());
            preparedStatement1.setString(11,account.getPhone());
            preparedStatement1.setString(12,account.getUsername());

            PreparedStatement preparedStatement2 = connection.prepareStatement(UPDATE_PROFILE);
            preparedStatement2.setString(1,account.getLanguagePreference());
            preparedStatement2.setString(2,account.getFavouriteCategoryId());
            preparedStatement2.setString(3,account.getUsername());

            if(preparedStatement1.executeUpdate()==1 &&
                    preparedStatement2.executeUpdate()==1)
                result=1;
            DBUtil.closePreparedStatement(preparedStatement1);
            DBUtil.closePreparedStatement(preparedStatement2);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public int updatePwd(Account account) {
        int result=0;
        try {
            Connection connection = DBUtil.getConnection();

            PreparedStatement preparedStatement3 = connection.prepareStatement(UPDATE_SIGNON);
            preparedStatement3.setString(1,account.getPassword());
            preparedStatement3.setString(2,account.getUsername());

            result=preparedStatement3.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement3);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public Account getUserByUsernameAndPassword(String username, String password) {
        Account account = null;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(FIND_USER_BY_USERNAME_AND_PASSWORD);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()) {
                account = new Account();
                resultSetToAccount(resultSet,account);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch(Exception e) {
            e.printStackTrace();
        }
        return account;
    }

    public void resultSetToAccount(ResultSet resultSet, Account account) {
        try {
            account.setUsername(resultSet.getString(1));
            account.setEmail(resultSet.getString(2));
            account.setFirstName(resultSet.getString(3));
            account.setLastName(resultSet.getString(4));
            account.setStatus(resultSet.getString(5));
            account.setAddress1(resultSet.getString(6));
            account.setAddress2(resultSet.getString(7));
            account.setCity(resultSet.getString(8));
            account.setState(resultSet.getString(9));
            account.setZip(resultSet.getString(10));
            account.setCountry(resultSet.getString(11));
            account.setPhone(resultSet.getString(12));
            account.setLanguagePreference(resultSet.getString(13));
            account.setFavouriteCategoryId(resultSet.getString(14));
            account.setListOption(resultSet.getInt(15)==1);
            account.setBannerOption(resultSet.getInt(16)==1);
            account.setBannerName(resultSet.getString(17));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
